﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using FileHelpers;
using System.Data;
using System.Data.SqlClient;


namespace TestJQGrid
{
    public partial class DataUpload : System.Web.UI.Page
    {
        #region init members
        private static DelimitedFileEngine dfengine;
        private static DataTable inputDt;
        private static DataTable rejectedDt;
        private static DataTable errorDt;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                initEngine(getTableName());
                manageButtons("Page_Load");
            }
        }

        private string getTableName()
        {
            if (Session["ProductName"] == null)
                return "CarSalesData";

            string product = Session["ProductName"].ToString();

            if (product == "CarSales")
                return "CarSalesData";
            else
            {
                throw new Exception("Invalid Product Name");
            }
        }

        private void initEngine(string tableName)
        {
            if (tableName == "CarSalesData")
            {
                dfengine = new DelimitedFileEngine(typeof(CarSalesData));
                //dfengine.SetProgressHandler(new ProgressChangeHandler(ProgressChange));
            }
            else
            {
                throw new Exception("Cannot initialize File Reader. Invalid Product Name.");
            }
        }
        #endregion

        #region buttons at work
        private void manageButtons(string source)
        {
            if (source == "ViewData")
            {
                SeperatorDDL.Enabled = false;
                FileUpload1.Enabled = false;
                ButtonViewData.Enabled = false;
                UploadButton.Enabled = true;
                ResetButton.Enabled = true;
                TabContainer1.ActiveTabIndex = 0;
            }
            if (source == "Page_Load")
            {
                UploadButton.Enabled = false;
                ResetButton.Enabled = false;
                ErrorButton.Enabled = false;
                TabContainer1.ActiveTabIndex = 0;
            }

            if (source == "Reset")
            {
                SeperatorDDL.Enabled = true;
                FileUpload1.Enabled = true;
                ButtonViewData.Enabled = true;
                ErrorButton.Enabled = false;
                UploadButton.Enabled = false;
                ResetButton.Enabled = false;
                TabContainer1.ActiveTabIndex = 0;
            }
        }

        protected void ButtonViewData_Click(object sender, EventArgs e)
        {
            if (!FileUpload1.HasFile)
                return;

            retrieveData();
            manageButtons("ViewData");
        }

        protected void ErrorButton_Click(object sender, EventArgs e)
        {
            if (errorDt != null)
            {
                Response.ContentType = "Error File (*.txt)";
                Response.AppendHeader("Content-Disposition", "attachment; filename=errors.txt");
                Response.TransmitFile(Server.MapPath("~\\errors.txt"));
                Response.End();
            }
        }

        protected void UploadButton_Click(object sender, EventArgs e)
        {
            string errorText = " Upload Success.";
            try
            {
                //save unbilled entries
                DataColumn status = new DataColumn("EntryStatus", System.Type.GetType("System.String"), "Unbilled");
                inputDt.Columns.Add(status);
                GlobalMethods.uploadDT2DB(inputDt, getTableName());

                //and also save rejected entries
                DataColumn rstatus = new DataColumn("EntryStatus", System.Type.GetType("System.String"), "Rejected");
                inputDt.Columns.Add(rstatus);
                GlobalMethods.uploadDT2DB(rejectedDt, getTableName());
            }
            catch (Exception)
            {
                errorText += " Ignoring Existing entries.";
            }
            WebMsgBox.Show(errorText);
            ResetButton_Click(sender, e);
        }

        protected void ResetButton_Click(object sender, EventArgs e)
        {
            manageButtons("Reset");

            //set all others to null;
            inputDt = null;
            rejectedDt = null;
            errorDt = null;
            dfengine = null;
            JQGridInput.DataSource = inputDt;
            JQGridInput.DataBind();
            JQGridRejected.DataSource = rejectedDt;
            JQGridRejected.DataBind();
            JQGridErrors.DataSource = errorDt;
            JQGridErrors.DataBind();
        }

        #endregion

        #region data operations
        private void retrieveData()
        {
            retrieveFileAsDataTable(FileUpload1.PostedFile);

            if (dfengine.ErrorManager.HasErrors)
                generateErrorDataTable();
            checkRejectedData();
        }

        void checkRejectedData()
        {
            //get list of city codes
            string query = "select [CityCode] from [StateCity] ";
            DataTable citylist = GlobalMethods.ExecuteSelectQuery(query);

            //get invalid enties
            var banned = from row in inputDt.AsEnumerable()
                         join city in citylist.AsEnumerable() on row.Field<string>("CityCode")
                         equals city.Field<string>("CityCode")
                         into j from x in j.DefaultIfEmpty() where x == null
                         select row;
            rejectedDt = banned.CopyToDataTable<DataRow>();

            //get valid entries
            var exceptBanned = from row in inputDt.AsEnumerable() 
                               join city in citylist.AsEnumerable() on row.Field<string>("CityCode") 
                               equals city.Field<string>("CityCode") 
                               into j from x in j.DefaultIfEmpty() where x != null 
                               select row;
            inputDt = exceptBanned.CopyToDataTable<DataRow>();
        }

        void retrieveFileAsDataTable(HttpPostedFile fileStream)
        {
            //initial setup
            TextReader tr = new StreamReader(fileStream.InputStream);
            dfengine.Options.Delimiter = this.SeperatorDDL.SelectedValue;
            dfengine.Options.IgnoreFirstLines = 1;
            //dfengine.Options.RecordCondition.Condition = RecordCondition.IncludeIfMatchRegex(
            dfengine.ErrorManager.ErrorMode = ErrorMode.SaveAndContinue;

            //read the file
            inputDt = dfengine.ReadStreamAsDT(tr);
            dfengine.ErrorManager.SaveErrors(Server.MapPath("~\\errors.txt"));
        }

        void generateErrorDataTable()
        {
            errorDt = new DataTable();
            ErrorButton.Enabled = true;
            errorDt.Columns.Add("LineNumber");
            errorDt.Columns.Add("LineString");
            errorDt.Columns.Add("ErrorDescription");
            errorDt.AcceptChanges();

            foreach (ErrorInfo err in dfengine.ErrorManager.Errors)
            {
                DataRow dr = errorDt.NewRow();
                dr[0] = err.LineNumber.ToString();
                dr[1] = err.RecordString.ToString();
                dr[2] = err.ExceptionInfo.Message;
                errorDt.Rows.Add(dr);
            }
            errorDt.AcceptChanges();
        }
        #endregion

        #region jqgrid display
        protected void JQGridInput_DataRequesting(object sender, Trirand.Web.UI.WebControls.JQGridDataRequestEventArgs e)
        {
            JQGridInput.DataSource = inputDt;
            JQGridInput.DataBind();
        }

        protected void JQGridErrors_DataRequesting(object sender, Trirand.Web.UI.WebControls.JQGridDataRequestEventArgs e)
        {
            JQGridErrors.DataSource = errorDt;
            JQGridErrors.DataBind();
        }

        protected void JQGridRejected_DataRequesting(object sender, Trirand.Web.UI.WebControls.JQGridDataRequestEventArgs e)
        {
            JQGridRejected.DataSource = rejectedDt;
            JQGridRejected.DataBind();
        }
        #endregion

    }
}